<?php
//select b1.gpname as name,b2.gpname as under from bs_groups as b1,bs_groups as b2 where b1.under=b2.id;
include_once($_SERVER['DOCUMENT_ROOT'].'/bs/include/header.inc');
include_once($_SERVER['DOCUMENT_ROOT'].'/bs/include/comp.inc');
if(isset($_GET['task']))
{
	if(!$link=mysql_connect("localhost",$user,$pass))
		die("Error in Database Connection");

	if(!mysql_select_db("bs"))
		echo("Error in Database Selection");

	if($_GET['task']=="display")
	{
?>
		| <a href="acc_info.php"> Accounts Info</a> | <a href="acc_info.php?info=ledger">Ledgers </a> | Display Ledgers</div>
		<h3>Ledgers</h3><br />
		<table cellpadding="8" border="1" class="main">
		<tr> <th><em><a href="ledger_man.php?task=display">Id</a></em> </th> <th><em><a href="ledger_man.php?task=display&sort=yes">Name</a></em> </th><th><em>Group</em></th></tr>
<?php
		if(isset($_GET['sort']))
				$result=mysql_query("select *,l.id as ldid from bs_ledgers as l,bs_groups as g where l.under=g.id order by ldname");
		else
				$result=mysql_query("select *,l.id as ldid from bs_ledgers as l,bs_groups as g where l.under=g.id");

		if(!$result)
			echo("Error in Query Execution");
		
		while($arr=mysql_fetch_array($result))
		{
			echo ("<tr><td><a href='ledger_man.php?task=edit_form&ldid={$arr['ldid']}'>{$arr['ldid']}</a></td><td>{$arr['ldname']}</td><td>{$arr['gpname']}</td></tr>");
		}
		
		echo "</table>";

	}
	
	if($_GET['task']=="addform")
	{
?>

		| <a href="acc_info.php"> Accounts Info</a> | <a href="acc_info.php?info=ledger">Ledgers </a> | Add Ledgers	</div>
		<h3>Add Ledger</h3>
		<table class="main">
		<form name="addform" action="<?php echo $_SERVER['PHP_SELF']?>" method="post">
		<tr><td><label>Ledger Name</label> </td><td> <input type="text" name="name" size="42" /></td></tr>
		<tr><td><label>Group</label> </td><td><select name="under">
								<?php 		
								$result=mysql_query("SELECT * FROM `bs_groups` ORDER BY `gpname` ASC LIMIT 0, 30 ");
								if(!$result)
									echo("Error in Query Execution");
								while($arr=mysql_fetch_array($result))
								{
									echo ("<option value='{$arr['id']}'>{$arr['gpname']}</option>");
								}
								?>
								</select>
								 </td></tr>
		<tr><td><label>Opening Balance</label></td><td><input type="text" name="ob" value="0" /> Rs</td></tr>
		<tr><td><label>Debit/Credit</label></td><td><select name="side">
									<option value="dr" selected="selected">Debit</option>
									<option value="cr">Credit</option>
								</select></td></tr>
		<tr><td><input type="submit" value="Add" /></td></tr>
		<input type="hidden" name="task" value="add" />
		</form>
		</table>
			
		
<?php
	}
	
	
	if($_GET['task']=="edit_form")
	{
		$ldid=$_GET['ldid'];
		
		$result=mysql_query("select * from bs_ledgers where id='{$ldid}'");
		if(!$result)
			echo "Error in query execution";
		else
			$previous=mysql_fetch_array($result);
			
		$tablename="bs_ledger_".ereg_replace(" ","_",$previous['ldname']);
		
		$result=mysql_query("select debit,credit from {$tablename} where voucher_type='OPB'");
		if(!$result)
			echo "Error in query execution";
		else
			$balance=mysql_fetch_array($result);
			
		if(!$balance)
		{
			$opb=0;
			$debit=1;
			$credit=0;
		}
		else
		{
			if($balance['credit']==0)
			{
				$opb=$balance['debit'];
				$debit=1;
				$credit=0;
			}
			else
			{
				$opb=$balance['credit'];
				$credit=1;
				$debit=0;
			}
		}
			
			
		
	
?>

		| <a href="acc_info.php"> Accounts Info</a> | <a href="acc_info.php?info=ledger">Ledgers </a> | Add Ledgers	</div>
		<h3>Edit Ledger : <?php echo $previous['ldname']?></h3>
		<table class="main">
		<form name="addform" action="<?php echo $_SERVER['PHP_SELF']?>" method="post">
		<tr><td><label>Ledger Name</label> </td><td> <input type="text" name="name" size="42" value="<?php echo $previous['ldname']?>"/></td></tr>
		<tr><td><label>Group</label> </td><td><select name="under">
								
								<?php 		
								$result=mysql_query("SELECT * FROM `bs_groups` ORDER BY `gpname` ASC LIMIT 0, 30 ");
								if(!$result)
									echo("Error in Query Execution");
								while($arr=mysql_fetch_array($result))
								{
									if($arr['id']==$previous['under'])
										echo ("<option value='{$arr['id']}' selected='selected'>{$arr['gpname']}</option>");
									else
										echo ("<option value='{$arr['id']}'>{$arr['gpname']}</option>");
								}
								?>
								</select>
								 </td></tr>
		<tr><td><label>Opening Balance</label></td><td><input type="text" name="ob" value="<?php echo (int)$opb?>" /> Rs</td></tr>
		<tr><td><label>Debit/Credit</label></td><td><select name="side">
									<?php if($debit==1)
									{
									?>
										<option value="dr" selected="selected">Debit</option>
										<option value="cr">Credit</option>
									<?php
									}
									else
									{
									?>
										<option value="dr">Debit</option>
										<option value="cr"  selected="selected">Credit</option>
									<?php
									}
									?>
								</select></td></tr>
		<tr><td><input type="submit" value="Update" /></td></tr>
		<input type="hidden" name="task" value="edit_add" />
		<input type="hidden" name="ldid" value="<?php echo $ldid ?>" />		
		</form>
		</table>
			
		
<?php
	}
}
else if(isset($_POST['task']))
{
	if(!$link=mysql_connect("localhost",$user,$pass))
		die("Error in Database Connection");

	if(!mysql_select_db("bs"))
		echo("Error in Database Selection");

	if($_POST['task']=="add")
	{
?>
		| <a href="acc_info.php"> Accounts Info</a> | <a href="acc_info.php?info=ledger">Ledger </a> | Add Ledger	</div>
		<h3>Add ledger</h3>

<?php
		$valid=1;
		if(empty($_POST['name']) or !ereg("^[0-9]+(.([0-9]){2,2})?$",$_POST['ob']))
		{
			echo "Check Your Input";
			$valid=0;
		}
		else
		{
			$query="select * from bs_ledgers";
			$result=mysql_query($query);
			while($arr=mysql_fetch_array($result))
			{
				if($_POST['name']==$arr['ldname'])
				{
					$valid=0;
					echo"Duplicate Entry";
					break;
				}
			}
		}
		if(!ereg("^[a-zA-Z0-9 \_]+$",$_POST['name']))
		{
			echo "<br>Special Characters are not allowed in Ledger Name except '_' ";
			$valid=0;
		}
			
			
		if($valid==1)
		{
			//insert into bs_ledgers	
			$query="insert into bs_ledgers (id,ldname,under) values(NULL,'{$_POST['name']}',{$_POST['under']})";
			$result=mysql_query($query);
			if(!$result)
				echo("Error in Query Execution");
			
			//create table				
			$tablename="bs_ledger_".ereg_replace(" ","_",$_POST['name']);
			$ob=(float)$_POST['ob'];
			$dt=date("Y-m-d");
			
			$query = "CREATE TABLE `{$tablename}` ("
				        . ' `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, '
				        . ' `voucher_type` VARCHAR(10) NOT NULL, '
						. ' `voucher_id` INT NOT NULL,'						
				        . ' `debit` DECIMAL(14,2) NOT NULL, '
				        . ' `credit` DECIMAL(14,2) NOT NULL, '
				        . ' `date` DATE NOT NULL '
					    . ' )'
				        . ' TYPE = myisam;';

			$result=mysql_query($query);
			if(!$result)
				echo("Error in Query Execution:".mysql_error());
			else
			{
				echo("Ledger Added Successfuly<br>");
//				echo mysql_insert_id();
			}
					
			//enter opening balance
			
			if($ob!=0)
			{
				if($_POST['side']=="dr")
				{
					$query="insert into {$tablename} (id,voucher_type,voucher_id,debit,credit,date) values(NULL,'OPB','NA','{$ob}','0.00','{$dt}')";
					$result=mysql_query($query);
					if(!$result)
						echo("Error in Query Execution");
				
				}
				else if($_POST['side']=="cr")
				{
					$query="insert into {$tablename} (id,voucher_type,voucher_id,debit,credit,date) values(NULL,'OPB','NA','0.00','{$ob}','{$dt}')";
					$result=mysql_query($query);
					if(!$result)
						echo("Error in Query Execution");
					}
				}
			}
			
			
	

		
		
		
	}
	
	
	if($_POST['task']=="edit_add")
	{
	
		$ldid=$_POST['ldid'];
		
		$result=mysql_query("select * from bs_ledgers where id='{$ldid}'");
		if(!$result)
			echo "Error in query execution";
		else
			$previous=mysql_fetch_array($result);
			
		$tablename="bs_ledger_".ereg_replace(" ","_",$previous['ldname']);
?>
		| <a href="acc_info.php"> Accounts Info</a> | <a href="acc_info.php?info=ledger">Ledger </a> | Add Ledger	</div>
		<h3>Edit ledger : <?php echo $previous['ldname']?></h3>


<?php
		$valid=1;
		if(empty($_POST['name']) or !ereg("^[0-9]+(.([0-9]){2,2})?$",$_POST['ob']))
		{
			echo "Check Your Input";
			$valid=0;
		}
		else
		{
			$query="select * from bs_ledgers where ldname<>'{$previous['ldname']}'";
			$result=mysql_query($query);
			while($arr=mysql_fetch_array($result))
			{
				if($_POST['name']==$arr['ldname'])
				{
					$valid=0;
					echo"Duplicate Entry<br>";
					break;
				}
			}
		}
		if(!ereg("^[a-zA-Z0-9 \_]+$",$_POST['name']))
		{
			echo "<br>Special Characters are not allowed in Ledger Name except '_' ";
			$valid=0;
		}
			
		
		if($valid==1)
		{
		
				//change 'under' if needed
				if($previous['under']!=$_POST['under'])
				{
						$result=mysql_query("update bs_ledgers as p set p.under='{$_POST['under']}' where id='{$ldid}'");
						if(!$result)
							echo "Error in Changing Group";
				}
		
				//change 'balance' if needed
		
				if($_POST['ob']==0)
				{
					//delete it
						$result=mysql_query("select id from {$tablename} where voucher_type='OPB'");
						if(!$result)
							echo "Error in deleting opening balance";
						else
							$arr=mysql_fetch_array($result);
				
						$result=mysql_query("delete from {$tablename} where id='{$arr['id']}'");
						if(!$result)
							echo "Error in deleting opening balance";
						

			
				}
				else
				{
					//delete it and insert new one
					
						$result=mysql_query("select id from {$tablename} where voucher_type='OPB'");
						if(!$result)
							echo "Error in deleting opening balance";
						else
							$arr=mysql_fetch_array($result);
				
						$result=mysql_query("delete from {$tablename} where id='{$arr['id']}'");
						if(!$result)
							echo "Error in deleting opening balance";
							
							
						$ob=(float)$_POST['ob'];
						
						$result=mysql_query("select min(date) as date from {$tablename}");
						if(!$result)
							echo "Error in deleting opening balance";
						else
							$arr=mysql_fetch_array($result);
							
						$result=mysql_query("select date_sub('{$arr['date']}',interval 1 day) as date from {$tablename}");
						if(!$result)
							echo "Error in deleting opening balance";
						else
							$arr=mysql_fetch_array($result);

						$dt=$arr['date'];
						
						//make an entry with new data
					
	
	
						if($_POST['side']=="dr")
						{
								$query="insert into {$tablename} (id,voucher_type,voucher_id,debit,credit,date) values(NULL,'OPB','NA','{$ob}','0.00','{$dt}')";
								$result=mysql_query($query);
								if(!$result)
									echo("Error in inserting opening balance");
				
						}
						else if($_POST['side']=="cr")
						{
								$query="insert into {$tablename} (id,voucher_type,voucher_id,debit,credit,date) values(NULL,'OPB','NA','0.00','{$ob}','{$dt}')";
								$result=mysql_query($query);
								if(!$result)
									echo("Error in opening balance");
						}
				}
		
			
		
			
			
				if($previous['ldname']!=$_POST['name'])
				{
						//update in bs ledgers
						$result=mysql_query("update bs_ledgers as p set p.ldname='{$_POST['name']}' where id='{$ldid}'");
						if(!$result)
								echo "Error in renaming ledger";

				
						//rename table
						$new_tablename="bs_ledger_".ereg_replace(" ","_",$_POST['name']);
				
						$result=mysql_query("ALTER TABLE `{$tablename}` RENAME `{$new_tablename}` ;");
						if(!$result)
								echo "Error in renaming ledger";
				}
				
				echo "Operation Successful";

		
	}//end of valid=1
	else
	{
		echo "Operation Fail";
	}
	
	
	}

}


include_once($_SERVER['DOCUMENT_ROOT'].'/bs/include/footer.inc');
?>
